﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace MDA
{
    /// <summary>
    /// 数据访问的公共接口。
    /// </summary>
    public class DataComman
    {
        public SqlConnection connection = new SqlConnection(ConnectData.ConnectString);

        /// <summary>
        /// 构造函数.
        /// </summary>
        public DataComman()
        {

        }

        /// <summary>
        /// 执行无返回的sql命令。
        /// </summary>
        /// <param name="com">命令对象。</param>
        public int ExecuteCommand(SqlCommand com)
        {
            com.Connection = connection;
            connection.Open();
            try
            {
                return com.ExecuteNonQuery();
            }
            finally
            {
                connection.Close();
            }
        }

        public int ExecuteCommand(string querystring)
        {
            SqlCommand com = new SqlCommand(querystring);
            return ExecuteCommand(com);
        }

        /// <summary>
        /// 执行有单一返回的sql命令。
        /// </summary>
        /// <param name="com">命令对象。</param>
        public object ExecuteScalar(SqlCommand com)
        {
            com.Connection = connection;
            connection.Open();
            try
            {
                object o = com.ExecuteScalar();
                return o;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// 执行有单一返回的sql命令。

        /// </summary>
        /// <param name="com">命令对象。</param>
        public object ExecuteScalar(string querystring)
        {
            SqlCommand com = new SqlCommand(querystring);
            return ExecuteScalar(com);
        }


        /// <summary>
        /// 在一个事务中处理多个命令。
        /// </summary>
        /// <param name="coms">命令对象。</param>
        /// <returns>如果成功则返回true.否则返回false</returns>
        public bool ExecuteCommand(params SqlCommand[] coms)
        {
            foreach (SqlCommand com in coms)
            {
                com.Connection = connection;
            }
            connection.Open();
            SqlTransaction tran = connection.BeginTransaction();
            foreach (SqlCommand com in coms)
            {
                com.Transaction = tran;
            }
            try
            {
                foreach (SqlCommand com in coms)
                {
                    com.ExecuteNonQuery();
                }
                tran.Commit();
                return true;
            }
            catch
            {
                tran.Rollback();
                return false;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// 执行有返回数据的sql命令。
        /// </summary>
        /// <param name="com">sql命令对象</param>
        /// <param name="dt">数据表</param>
        public void SelectCommand(DataTable dt, SqlCommand com)
        {
            com.Connection = connection;
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = com;

            com.Connection.Open();
            SqlTransaction tran = com.Connection.BeginTransaction();
            com.Transaction = tran;
            try
            {
                sda.Fill(dt);
                tran.Commit();
            }
            catch
            {
                tran.Rollback();
            }
            finally
            {
                com.Connection.Close();
            }

        }

        /// <summary>
        /// 执行有返回数据的sql命令。

        /// </summary>
        /// <param name="com">sql命令对象</param>
        /// <param name="dt">数据表</param>
        public void SelectCommand(DataTable dt, string querystring)
        {
            SqlCommand com = new SqlCommand(querystring);
            com.Connection = connection;
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = com;

            com.Connection.Open();
            SqlTransaction tran = com.Connection.BeginTransaction();
            com.Transaction = tran;
            try
            {
                sda.Fill(dt);
                tran.Commit();
            }
            catch
            {
                tran.Rollback();
            }
            finally
            {
                com.Connection.Close();
            }

        }

        /// <summary>
        /// 执行有返回的sql命令。
        /// </summary>
        /// <param name="com">sql命令对象</param>
        ///<returns>查询结果表</returns>
        public DataTable SelectCommand(SqlCommand com)
        {
            com.Connection = connection;
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = com;

            DataTable dt = new DataTable();
            connection.Open();
            SqlTransaction tran = connection.BeginTransaction();
            com.Transaction = tran;
            try
            {
                sda.Fill(dt);
                tran.Commit();
            }
            catch
            {
                tran.Rollback();
            }
            finally
            {
                connection.Close();
            }
            return dt;
        }

        /// <summary>
        /// 执行有返回的sql命令。

        /// </summary>
        /// <param name="com">sql命令对象</param>
        ///<returns>查询结果表</returns>
        public DataTable SelectCommand(string querystring)
        {
            SqlCommand com = new SqlCommand(querystring);
            com.Connection = connection;
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = com;

            DataTable dt = new DataTable();
            connection.Open();
            SqlTransaction tran = connection.BeginTransaction();
            com.Transaction = tran;
            try
            {
                sda.Fill(dt);
                tran.Commit();
            }
            catch
            {
                tran.Rollback();
            }
            finally
            {
                connection.Close();
            }
            return dt;
        }

        /// <summary>
        /// 从视图根据条件查询某页数据。
        /// </summary>
        /// <param name="dt">作为返回的表</param>
        /// <param name="viewName">视图名称</param>
        /// <param name="condition">参数条件</param>
        /// <param name="perPageSize">每页数据量</param>
        /// <param name="pageNum">当前第几页,从0开始</param>
        /// <param name="orderFieldName">排序字段</param>
        /// <param name="orderSort">排序方式</param>
        /// <returns></returns>
        public void GetViewData(DataTable dt, string viewName, string condition, int perPageSize, int pageNum, string orderFieldName, string orderSort)
        {
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("select identity(int,1,1) as rowid ,* into temp" + viewName + " from " + viewName);
            if (condition != "")
                sb.Append(" where " + condition);
            sb.Append("  order by ");
            sb.Append(orderFieldName);
            sb.Append("  ");
            sb.Append(orderSort);
            int begin = perPageSize * pageNum;
            int end = perPageSize * (pageNum + 1);
            sb.Append(" select * from temp" + viewName + " where rowid>" + begin.ToString() + " and rowid<=" + end.ToString());
            sb.Append("  drop table temp" + viewName);
            SqlCommand com = new SqlCommand(sb.ToString());
            SelectCommand(dt, com);
        }

        /// <summary>
        /// 从指定的表中获取当前页的数据。
        /// </summary>
        /// <param name="originalTable">原始表数据</param>
        /// <param name="currentTable">当前页数据，作为运行结果</param>
        /// <param name="perPageSize">每页数据条数</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="orderFieldName">排序字段</param>
        /// <param name="orderSort">排序方式</param>
        public void GetCurrentPageData(DataTable originalTable, DataTable currentTable, int perPageSize, int currentPage, string orderFieldName, string orderSort)
        {
            if (currentTable == null) currentTable = new DataTable();
            if (originalTable.Columns.Count == 0)
            {
                currentTable = originalTable;
                return;
            }

            DataView dv = new DataView(originalTable);
            dv.Sort = orderFieldName + " " + orderSort;

            currentTable.Columns.Add("RowNo", typeof(int));
            currentTable.Columns["RowNo"].AutoIncrement = true;
            currentTable.Columns["RowNo"].AutoIncrementSeed = 1;
            currentTable.Columns["RowNo"].AutoIncrementStep = 1;

            foreach (DataColumn dcc in originalTable.Columns)
            {
                currentTable.Columns.Add(dcc.ColumnName, dcc.DataType);
            }

            int begin = perPageSize * currentPage;
            int end = perPageSize * (currentPage + 1);

            while (begin < end && begin < dv.Count)
            {
                DataRow dr = currentTable.NewRow();
                foreach (DataColumn dcc in originalTable.Columns)
                {
                    dr[dcc.ColumnName] = dv[begin][dcc.ColumnName];
                }
                currentTable.Rows.Add(dr);
                begin++;
            }
        }

        /// <summary>
        /// 从指定的表中获取当前页的数据,并获取总数据量，总页数。
        /// </summary>
        /// <param name="originalTable">原始表数据</param>
        /// <param name="currentTable">当前页数据，作为运行结果</param>
        /// <param name="perPageSize">每页数据条数</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="orderFieldName">排序字段</param>
        /// <param name="orderSort">排序方式</param>
        /// <param name="pageNums">总页面数</param>
        /// <param name="totalRows">总记录数</param>
        public void GetCurrentPageData(DataTable originalTable, DataTable currentTable, int perPageSize, int currentPage, string orderFieldName, string orderSort, out int totalRows, out int pageNums)
        {
            totalRows = originalTable.Rows.Count;
            int d = totalRows / perPageSize;
            if (totalRows % perPageSize != 0) d++;
            pageNums = d;
            if (currentTable == null) currentTable = new DataTable();

            if (originalTable.Columns.Count == 0)
            {
                currentTable = originalTable;
                return;
            }

            DataView dv = new DataView(originalTable);
            dv.Sort = orderFieldName + " " + orderSort;

            currentTable.Columns.Add("RowNo", typeof(int));
            currentTable.Columns["RowNo"].AutoIncrement = true;
            currentTable.Columns["RowNo"].AutoIncrementSeed = 1;
            currentTable.Columns["RowNo"].AutoIncrementStep = 1;

            foreach (DataColumn dcc in originalTable.Columns)
            {
                currentTable.Columns.Add(dcc.ColumnName, dcc.DataType);
            }

            int begin = perPageSize * currentPage;
            int end = perPageSize * (currentPage + 1);

            while (begin < end && begin < dv.Count)
            {
                DataRow dr = currentTable.NewRow();
                foreach (DataColumn dcc in originalTable.Columns)
                {
                    dr[dcc.ColumnName] = dv[begin][dcc.ColumnName];
                }
                currentTable.Rows.Add(dr);
                begin++;
            }
        }

        /// <summary>
        /// 根据Sql命令对象获取当前页的数据,并获取总数据量，总页数。
        /// </summary>
        /// <param name="command">Sql命令对象</param>
        /// <param name="currentTable">当前页数据，作为运行结果</param>
        /// <param name="perPageSize">每页数据条数</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="orderFieldName">排序字段</param>
        /// <param name="orderSort">排序方式</param>
        /// <param name="pageNums">总页面数</param>
        /// <param name="totalRows">总记录数</param>
        public void GetCurrentPageData(SqlCommand command, DataTable currentTable, int perPageSize, int currentPage, string orderFieldName, string orderSort, out int totalRows, out int pageNums)
        {
            DataTable originalTable = new DataTable();

            this.SelectCommand(originalTable, command);
            totalRows = originalTable.Rows.Count;
            int d = totalRows / perPageSize;
            if (totalRows % perPageSize != 0) d++;
            pageNums = d;


            if (currentTable == null) currentTable = new DataTable();

            if (originalTable.Columns.Count == 0)
            {
                currentTable = originalTable;
                return;
            }

            DataView dv = new DataView(originalTable);
            dv.Sort = orderFieldName + " " + orderSort;



            foreach (DataColumn dcc in originalTable.Columns)
            {
                currentTable.Columns.Add(dcc.ColumnName, dcc.DataType);
            }
            currentTable.Columns.Add("RowNo", typeof(int));
            currentTable.Columns["RowNo"].AutoIncrement = true;
            currentTable.Columns["RowNo"].AutoIncrementSeed = 1;
            currentTable.Columns["RowNo"].AutoIncrementStep = 1;

            int begin = perPageSize * currentPage;
            int end = perPageSize * (currentPage + 1);

            while (begin < end && begin < dv.Count)
            {
                DataRow dr = currentTable.NewRow();
                foreach (DataColumn dcc in originalTable.Columns)
                {
                    dr[dcc.ColumnName] = dv[begin][dcc.ColumnName];
                }
                currentTable.Rows.Add(dr);
                begin++;
            }
        }


        /// <summary>
        /// 根据Sql命令对象获取当前页的数据,并获取总数据量，总页数。自动处理行号为总体行号.
        /// </summary>
        /// <param name="command">Sql命令对象</param>
        /// <param name="currentTable">当前页数据，作为运行结果</param>
        /// <param name="perPageSize">每页数据条数</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="orderFieldName">排序字段</param>
        /// <param name="orderSort">排序方式</param>
        /// <param name="pageNums">总页面数</param>
        /// <param name="totalRows">总记录数</param>
        public void GetCurrentPageData2(SqlCommand command, DataTable currentTable, int perPageSize, int currentPage, string orderFieldName, string orderSort, out int totalRows, out int pageNums)
        {

            DataTable originalTable = new DataTable();
            originalTable.Columns.Add("RowNo", typeof(int));
            originalTable.Columns["RowNo"].AutoIncrement = true;
            originalTable.Columns["RowNo"].AutoIncrementSeed = 1;
            originalTable.Columns["RowNo"].AutoIncrementStep = 1;

            this.SelectCommand(originalTable, command);
            totalRows = originalTable.Rows.Count;
            int d = totalRows / perPageSize;
            if (totalRows % perPageSize != 0) d++;
            pageNums = d;


            if (currentTable == null) currentTable = new DataTable();

            if (originalTable.Columns.Count == 0)
            {
                currentTable = originalTable;
                return;
            }

            DataView dv = new DataView(originalTable);
            dv.Sort = orderFieldName + " " + orderSort;



            foreach (DataColumn dcc in originalTable.Columns)
            {
                currentTable.Columns.Add(dcc.ColumnName, dcc.DataType);
            }


            int begin = perPageSize * currentPage;
            int end = perPageSize * (currentPage + 1);

            while (begin < end && begin < dv.Count)
            {
                DataRow dr = currentTable.NewRow();
                foreach (DataColumn dcc in originalTable.Columns)
                {
                    dr[dcc.ColumnName] = dv[begin][dcc.ColumnName];
                }
                currentTable.Rows.Add(dr);
                begin++;
            }
        }

        /// <summary>
        /// 根据条件获取视图共有多少页
        /// </summary>
        /// <param name="viewName">视图名称</param>
        /// <param name="condition">条件</param>
        /// <param name="perPageSize">每页数据量</param>
        /// <returns></returns>
        public int TotalViewPage(string viewName, string condition, int perPageSize)
        {
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("select count(*)  from " + viewName);
            if (condition != "")
                sb.Append(" where " + condition);


            SqlConnection con = new SqlConnection(ConnectData.ConnectString);
            SqlCommand com = new SqlCommand(sb.ToString(), con);


            con.Open();
            object o = com.ExecuteScalar();
            con.Close();
            int totalRecord = (int)o;
            int d = totalRecord / perPageSize;
            if (totalRecord % perPageSize != 0) d++;
            return d;
        }
    }
}